//
//  DataBase_Bookmark.m
//  USB_Reader
//
//  Created by xiaozao on 15/3/30.
//  Copyright (c) 2015年 Tony_Wang. All rights reserved.
//

#import "DataBase_Bookmark.h"
#import "MainBookmark.h"

@implementation DataBase_Bookmark


+ (DataBase_Bookmark *)shareInstance
{
    // 单例方法实现.
    static DataBase_Bookmark *dbHandler = nil;
    
    // 当静态指针为空得时候, 创建一个对象
    if (dbHandler == nil) {
        dbHandler = [[DataBase_Bookmark alloc] init];
    }
    
    // 不为空直接将对象返回.
    [dbHandler openDB];
    [dbHandler createBookMarkTabel];
    return dbHandler;
}

- (BOOL)judgeResult:(int)result type:(NSString *)type
{
    if (result == SQLITE_OK) {
        NSLog(@"%@成功", type);
        return YES;
    } else {
        NSLog(@"%@失败, 失败编号: %d", type, result);
        return NO;
    }
}

// 打开数据库
- (BOOL)openDB
{
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *dbPath = [docPath stringByAppendingPathComponent:@"Bookmark.db"];
    
    NSLog(@"DataBase_Bookmark DBPath: %@", dbPath);
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    
    NSLog(@"DataBase_Bookmark 打开数据库的结果为: %d", result);
    
    return [self judgeResult:result type:@"打开数据库"];
}


// 关闭数据库
- (BOOL)closeDB
{
    int result = sqlite3_close(dbPoint);
    return [self judgeResult:result type:@"关闭数据库"];
}


- (BOOL)helperWithSqlStr:(NSString *)sqlStr type:(NSString *)type
{
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    
    return [self judgeResult:result type:type];
}


// 创建学生表
- (BOOL)createBookMarkTabel
{
    NSString *sqlStr = @"create table Bookmark (offset CGFloat, tag CGFloat)";
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    return  [self judgeResult:result type:@"创建学生表"];
}

// 添加书签
- (BOOL)insertBookmark:(MainBookmark *)Bookmark
{
    // insert into 表名 (列名1. -- values ('文本' / 数字)
    NSString *sqlStr = [NSString stringWithFormat:@"insert into Bookmark values (%lf, %lf)", Bookmark.offset, Bookmark.tag];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    return [self judgeResult:result type:@"添加书签"];
}

// 修改书签/ 更新书签
- (BOOL)updateBookmark:(MainBookmark *)Bookmark
{
    NSString *sqlStr = [NSString stringWithFormat:@"update Bookmark set offset = %lf, tag  = %lf where tag  = 1000", Bookmark.offset, Bookmark.tag];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    return [self judgeResult:result type:@"更新书签"];
}

// 删除书签
- (BOOL)deleteBookmark:(MainBookmark *)Bookmark
{
    NSString *sqlStr = [NSString stringWithFormat:@"delete from Bookmark where right = %lf", Bookmark.tag];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    return [self judgeResult:result type:@"删除书签"];
}

// show bookmark
-(NSMutableArray *)selectAllBookmark
{
    NSString *sqlStr = @"select *from Bookmark";
    // 数据库的状态指针, 数据库执行sql语句的所有结果都保存在这个指针中
    sqlite3_stmt *stmt = nil;
    
    // 执行sql语句, 把sql语句的执行结果保存在stmt中
    // 参数1:数据库指针,
    // 参数2: 数据库sql语句
    // 参数3:限制sql语句长度, -1表示不限制
    // 参数4: stmt的指针地址
    int result = sqlite3_prepare(dbPoint, [sqlStr UTF8String], -1, &stmt, nil);
    NSMutableArray *bookMarkArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            MainBookmark *bookmark = [[MainBookmark alloc] init];
            // 按照列的顺序读取
            // 参数1 状态指针
            // 参数2 从第几列中取值
            bookmark.offset = sqlite3_column_double(stmt, 0);
            bookmark.tag = sqlite3_column_double(stmt, 1);
            
            [bookMarkArr addObject:bookmark];
            [bookmark release];
        }
    }
    sqlite3_finalize(stmt);
    return bookMarkArr;
}










@end
